/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.zuq.geocode;

import com.gisgraphy.domain.geoloc.service.geoloc.street.StreetType;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * An implementation of GeocodeService that queries a PostGIS prepared database
 * with an openstreetmap table in the gisgraphy schema.
 * 
 * @author zuq
 */
public class PostgisGeocodeService implements ReverseGeocodeService {

    // Constants ---------------------------------------------------------------

    private static final Logger LOGGER = LoggerFactory.getLogger(PostgisGeocodeService.class);

    private final String nearStreetStm = ""
            + "SELECT gid, name, length, streettype, oneway, distance(shape, geometryfromtext(? , 4326)) as d "
            + "FROM openstreetmap "
            + "WHERE (shape && expand(setsrid(makepoint( ? , ? ), 4326), ?)) "
            + "AND distance(shape, geometryfromtext(?, 4326)) < ? "
            + "ORDER BY d LIMIT 10";
    private final DataSource dataSource;

    // Constructors ------------------------------------------------------------

    public PostgisGeocodeService(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public PostgisGeocodeService(String url, String user, String password) {
        ComboPooledDataSource cpds = new ComboPooledDataSource();

        try {
            cpds.setDriverClass("org.postgresql.Driver");
        } catch (PropertyVetoException ex) {
            ex.printStackTrace();
        }

        //loads the jdbc driver
        cpds.setJdbcUrl(url);
        cpds.setUser(user);
        cpds.setPassword(password);

        // the settings below are optional -- c3p0 can work with defaults
        cpds.setMinPoolSize(5);
        cpds.setMaxPoolSize(25);
        cpds.setAcquireIncrement(5);
        cpds.setMaxPoolSize(20);

        dataSource = cpds;
    }

    // Public methods ----------------------------------------------------------

    // Getters & setters -------------------------------------------------------

    public DataSource getDataSource() {
        return dataSource;
    }

    // Implements br.com.zuq.geocode.GeocodeService ----------------------------
    /**
     * @see GeocodeService#findNearestStreets(double, double, double)
     * 
     * @param lat
     * @param lng
     * @param radius
     * @return
     */
    public List<NearStreet> findNearestStreets(double lat, double lng, double radius) {

        List<NearStreet> result = new ArrayList<NearStreet>();

        Connection conn = null;
        PreparedStatement stm = null;
        try {

            String point = "POINT (" + lng + " " + lat + ")";

            conn = dataSource.getConnection();
            stm = conn.prepareCall(nearStreetStm);

            stm.setString(1, point);
            stm.setDouble(2, lng);
            stm.setDouble(3, lat);
            stm.setDouble(4, radius);
            stm.setString(5, point);
            stm.setDouble(6, radius);

            ResultSet rs = stm.executeQuery();

            conn.commit();
            NearStreet street;
            while (rs.next()) {
                street = new NearStreet();

                street.gid = rs.getLong("gid");
                street.name = rs.getString("name");
                street.length = rs.getDouble("length");
                street.distance = rs.getDouble("d");
                street.oneWay = rs.getBoolean("oneway");

                result.add(street);
            }

        } catch (SQLException ex) {

            LOGGER.warn("Error while executing postgis query", ex);

        } finally {

            try {
                if (stm != null) {

                    stm.close();

                }

                if (conn != null) {

                    conn.close();

                }

            } catch (SQLException ex) {
                LOGGER.warn("Error while closing statement and connection", ex);
            }
        }

        return result;
    }

    public List<NearStreet> findNearestStreets(double lat, double lng,
            double radious, StreetType type) {

        return findNearestStreets(lat, lng, radious, type, null, Boolean.TRUE);

    }

    public List<NearStreet> findNearestStreets(double lat, double lng,
            double radious, StreetType type, String name, Boolean oneWay) {

        throw new UnsupportedOperationException();

    }
}


